違反完整性約束 - 調用存儲過程時未找到父鍵 (Integrity constraint violated - parent key not found when calling stored procedure)

問題描述

違反完整性約束 ‑ 調用存儲過程時未找到父鍵 (Integrity constraint violated ‑ parent key not found when calling stored procedure)

我正在嘗試在 Oracle 11g XE 中執行存儲包過程調用，但由於某種原因，我收到以下錯誤：

錯誤報告 ‑ ORA‑02291：完整性約束（ROOT .SYS_C007057) 違反 ‑ 未找到父鍵 ORA‑06512：在“ROOT.BOOKS_STORE”，第 69 行 ORA‑06512：在第 2 行

02291. 00000 ‑ "integrity constraint (%s.%s) violated ‑ parent key not found"

*原因：外鍵值沒有匹配的主鍵值.

*動作：刪除外鍵或添加匹配的主鍵。

調用以下程序時：

begin
  books_store.add_books_to_store(
    'To Kill a Mockingbird', 21,
    'test description', 5,
    'https://test_img.jpg',
    10, 6.99
  );
end;

什麼程序所做的是將數據插入到 books 表中。下面是過程文本（在 books_store 包）和 books 表的描述。

  procedure add_books_to_store(
    book_name books.name%type, book_author_id books.author_id%type,
    book_description books.description%type default null,
    book_publisher_id books.publisher_id%type, book_cover_img books.cover_img%type,
    books_count books.available_count%type, book_price books.price%type)
    is
    existing_books_count integer;
    add_negative_or_zero_books exception;
    begin
      if books_count <= 0 then
        raise add_negative_or_zero_books;
      end if;

      select count(*) into existing_books_count from books
      where
        name = book_name and author_id = book_author_id and
        description = book_description and publisher_id = book_publisher_id and
        price = book_price;

      if existing_books_count = 0 then
        insert into books values (books_seq.nextval, book_name, book_description,
          book_cover_img, books_count, book_author_id, book_publisher_id, book_price);
      else
        update books set available_count = available_count + books_count
          where
            name = book_name and author_id = book_author_id and
            description = book_description and publisher_id = book_publisher_id and
            price = book_price;
      end if;

      exception
        when add_negative_or_zero_books then
          raise_application_error(‑10003, 'You cannot add 0 or less books');
    end add_books_to_store;

books 描述：

 DESC books;
 Name                   Null?        Type
 ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
 ID                    NOT NULL     NUMBER(5)
 NAME                  NOT NULL     VARCHAR2(200)
 DESCRIPTION                        VARCHAR2(2000)
 COVER_IMG                          VARCHAR2(300)
 AVAILABLE_COUNT       NOT NULL     NUMBER(4)
 PRICE                              NUMBER(10,2)
 AUTHOR_ID                          NUMBER(5)
 PUBLISHER_ID                       NUMBER(5)

所以，錯誤說我的主鍵或外鍵有問題。不過，我不明白到底出了什麼問題。

我認為問題在於我將錯誤的 author_id 和 publisher_id 作為參數傳遞給該過程，但他們是正確的。這是 authors 和 publishers 表的 select * 調用：

select * from authors;
    ID FIRST_NAME       LAST_NAME        BIRTHDAY
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
    21   Harper           Lee            28‑APR‑26

select * from publishers;
    ID            NAME
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
     5      Penguin Fiction

你能幫我找出來嗎我的代碼有什麼問題以及如何使它工作？

PS：這裡'

---

參考解法

方法 1:

I think, problem could be here:

  insert into books values (books_seq.nextval, book_name, book_description,
      book_cover_img, books_count, book_author_id, book_publisher_id, book_price);

Because in table description columns have another order:

PRICE                              NUMBER(10,2) 
AUTHOR_ID                          NUMBER(5)
PUBLISHER_ID                       NUMBER(5)

Try to specify column names explicitly:

insert into books (ID, NAME, DESCRIPTION, COVER_IMG, AVAILABLE_COUNT, PRICE, AUTHOR_ID, PUBLISHER_ID)
values (books_seq.nextval, book_name, book_description,
      book_cover_img, books_count, book_price, book_author_id, book_publisher_id);

Now it looks like you are inserting AUTHOR_ID value into PRICE column, PUBLISHER_ID into AUTHOR_ID and PRICE into PUBLISHER_ID.

(by Denis Yakovenko、Dmitriy)

參考文件

1. Integrity constraint violated ‑ parent key not found when calling stored procedure (CC BY‑SA 2.5/3.0/4.0)